﻿using System;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using RED_NET_Core.Helper;
using RED_NET_Core.Entity;
using System.IO;

/// <summary>
///Album_Photo 的摘要说明
/// </summary>
namespace RED_NET_Core.DAL
{
    public class Album_Photo
    {
        public Album_Photo()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
        }

        //相册操作

        #region[获取对应用户的所有相册]

        /// <summary>
        /// 获取对应用户的相册列表
        /// </summary>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public DataTable GetAllAlbumByUser(int UserId)
        {
            SqlHelper Helper = new SqlHelper();
            string CmdText = " SELECT * FROM _Album WHERE _AlbumOfUserId = @UserId";
            SqlParameter[] parm = new SqlParameter[]
            {
            new SqlParameter("@UserId",UserId)
            };
            try
            {
                SqlCommand mycommand = Helper.GetCommand(CmdText, parm);
                DataTable dt = new DataTable();
                using (SqlDataReader mysqldatareader = mycommand.ExecuteReader())
                {
                    dt.Load(mysqldatareader);
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[增加相册]

        /// <summary>
        /// 增加相册
        /// </summary>
        /// <param name="Album_Name"></param>
        /// <param name="Album_Info"></param>
        /// <param name="Album_UpdateTime"></param>
        /// <param name="UserId"></param>
        /// <param name="CoverPhotoUrl"></param>
        public void AddAlbum(string Album_Name, string Album_Info, DateTime Album_UpdateTime, int UserId, string CoverPhotoUrl)
        {
            SqlHelper Helper = new SqlHelper();
            string CmdText = "INSERT INTO _Album(_AlbumName,_AlbumContent,_AlbumAddTime,_AlbumUpdateTime,_AlbumOfUserId,_AlbumUrl) VALUES(@Name,@Info,@Time,@Time,@UId,@Url)";
            SqlParameter[] parm = new SqlParameter[]
            {
            new SqlParameter("@Name",Album_Name),
            new SqlParameter("@Info",Album_Info),
            new SqlParameter("@Time",Album_UpdateTime),
            new SqlParameter("@UId",UserId),
            new SqlParameter("@Url",CoverPhotoUrl)
            };
            SqlCommand mycommand = Helper.GetCommand(CmdText, parm);
            try
            {
                mycommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region [更新相册]

        /// <summary>
        /// 更新相册
        /// </summary>
        /// <param name="Album_ID"></param>
        /// <param name="Album_Name"></param>
        /// <param name="Album_Info"></param>
        /// <param name="Album_UpdateTime"></param>
        /// <param name="UserId"></param>
        /// <param name="CoverPhotoUrl"></param>
        public void UpdateAlbum(int Album_ID, string Album_Name, string Album_Info, DateTime Album_UpdateTime, string CoverPhotoUrl)
        {
            SqlHelper Helper = new SqlHelper();
            string CmdText = "UPDATE _Album SET _AlbumName=@Name,_AlbumContent=@Info,_AlbumUpdateTime=@Time,_AlbumUrl=@Url WHERE _AlbumId=@Id";
            SqlParameter[] parm = new SqlParameter[]
            {
            new SqlParameter("@Id",Album_ID),
            new SqlParameter("@Name",Album_Name),
            new SqlParameter("@Info",Album_Info),
            new SqlParameter("@Time",Album_UpdateTime),
            new SqlParameter("@Url",CoverPhotoUrl)
            };
            SqlCommand mycommand = Helper.GetCommand(CmdText, parm);
            try
            {
                mycommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region [删除相册]

        /// <summary>
        /// 删除相册
        /// </summary>
        /// <param name="Album_ID"></param>
        public void DeleteAlbum(int Album_ID)
        {
            SqlHelper Helper = new SqlHelper();
            Album_Photo album_photo = new Album_Photo();
            DataTable dt = album_photo.GetAllPhotoByAlbum(Album_ID);

            string CmdText = "DELETE _Album WHERE _AlbumId=@Id";
            SqlParameter[] parm = new SqlParameter[]
            {
            new SqlParameter("@Id",Album_ID)
            };

            AlbumClass album = album_photo.GetAlbumInf(Album_ID);
            if (album != null)
            {
                System.IO.FileInfo file;
                string path = System.Web.HttpContext.Current.Server.MapPath(album.AlbumUrl);//项目的程序集里必须这么用
                file = new System.IO.FileInfo(path);
                if (file.Exists) file.Delete();
            }

            SqlCommand mycommand = Helper.GetCommand(CmdText, parm);
            try
            {
                mycommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region[获取对应相册的所有信息]

        public AlbumClass GetAlbumInf(int AlbumId)
        {

            AlbumClass album;
            SqlHelper Helper = new SqlHelper();
            string CmdText = "SELECT * FROM _Album WHERE _AlbumId =@AId";
            SqlParameter[] parm = new SqlParameter[]
            {
                new SqlParameter("@AId",AlbumId)
            };

            SqlCommand mycommand = Helper.GetCommand(CmdText, parm);
            try
            {
                SqlDataReader dr = mycommand.ExecuteReader();
                if (dr.Read())
                {
                    album = new AlbumClass(Convert.ToInt32(dr["_AlbumId"]), Convert.ToInt32(dr["_AlbumOfUserId"]),
                        dr["_AlbumName"].ToString(), dr["_AlbumContent"].ToString(), dr["_AlbumUrl"].ToString(),
                        DateTime.Parse(Convert.ToString(dr["_AlbumAddTime"])), DateTime.Parse(Convert.ToString(dr["_AlbumUpdateTime"])));
                    return album;
                }
                else return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region [获取对应相册的所有照片]

        /// <summary>
        /// 获取相应相册里的所有照片
        /// </summary>
        /// <param name="Album_Id"></param>
        /// <returns></returns>
        public DataTable GetAllPhotoByAlbum(int Album_Id)
        {
            SqlHelper Helper = new SqlHelper();
            string CmdText = "SELECT * FROM _Photo WHERE _PhotoOfAlbumId = @AId";
            SqlParameter[] parm = new SqlParameter[]
            {
            new SqlParameter("@AId",Album_Id)
            };
            SqlCommand mycommand = Helper.GetCommand(CmdText, parm);
            DataTable dt = new DataTable();
            try
            {
                using (SqlDataReader mysqldatareader = mycommand.ExecuteReader())
                {
                    dt.Load(mysqldatareader);
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region [获取对应相册的所有照片的URL]

        /// <summary>
        /// 获取相应相册里的所有照片的链接地址
        /// </summary>
        /// <param name="Album_Id"></param>
        /// <returns></returns>
        public ArrayList GetAllPhotoByAlbum_JS(int Album_Id)
        {
            SqlHelper Helper = new SqlHelper();
            string CmdText = "SELECT _PhotoUrl FROM _Photo WHERE _PhotoOfAlbumId = @AId";
            SqlParameter[] parm = new SqlParameter[]
            {
            new SqlParameter("@AId",Album_Id)
            };
            SqlCommand mycommand = Helper.GetCommand(CmdText, parm);
            SqlDataReader dr;
            ArrayList PhotoUrl = new ArrayList() ;
            int num = 0;
            try
            {
                dr = mycommand.ExecuteReader();
                while (dr.Read())
                {
                    PhotoUrl.Add(dr["_PhotoUrl"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return PhotoUrl;
        }

        #endregion

        #region [获取对应相册的所有照片缩略图的URL]

        /// <summary>
        /// 获取相应相册里的所有照片缩略图的链接地址
        /// </summary>
        /// <param name="Album_Id"></param>
        /// <returns></returns>
        public ArrayList GetAllSmallPhotoByAlbum_JS(int Album_Id)
        {
            SqlHelper Helper = new SqlHelper();
            string CmdText = "SELECT _PhotoSmallUrl FROM _Photo WHERE _PhotoOfAlbumId = @AId";
            SqlParameter[] parm = new SqlParameter[]
            {
            new SqlParameter("@AId",Album_Id)
            };
            SqlCommand mycommand = Helper.GetCommand(CmdText, parm);
            SqlDataReader dr;
            ArrayList PhotoUrl = new ArrayList();
            int num = 0;
            try
            {
                dr = mycommand.ExecuteReader();
                while (dr.Read())
                {
                    PhotoUrl.Add(dr["_PhotoSmallUrl"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return PhotoUrl;
        }

        #endregion

        #region [删除对应相册的所有图片]

        /// <summary>
        /// 删除图片信息
        /// </summary>
        /// <param name="PhotoId"></param>
        public void DeletePhotoOfAlbum(int AlbumId)
        {
            Album_Photo album_photo = new Album_Photo();
            try
            {
                DataTable dt = album_photo.GetAllPhotoByAlbum(AlbumId);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    album_photo.DeletePhoto(Int32.Parse(dt.Rows[i]["_PhotoId"].ToString()));
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        //照片的操作

        #region [添加照片]

        /// <summary>
        /// 添加照片
        /// </summary>
        /// <param name="Album_Id"></param>
        /// <param name="Info"></param>
        /// <param name="Time"></param>
        /// <param name="PhotoUrl"></param>
        public int AddPhoto(int Album_Id, string Name, string Info, DateTime Time, string PhotoUrl,string SmallPhotoUrl,int User_Id)
        {
            SqlHelper Helper = new SqlHelper();
            string CmdText = "INSERT INTO _Photo(_PhotoName,_PhotoContent,_PhotoOfAlbumId,_PhotoAddTime,_PhotoUrl," +
                "_PhotoUpdateTime,_PhotoSmallUrl,_PhotoOfUId) VALUES(@Name,@Info,@AlbumId,@Time,@Url,@Time,@SmallPhotoUrl,@UserId)" +
                "SELECT @@identity as _PhotoId";
            SqlParameter[] parm = new SqlParameter[]
            {
            new SqlParameter("@Name",Name),
            new SqlParameter("@Info",Info),
            new SqlParameter("@AlbumId",Album_Id),
            new SqlParameter("@Time",Time),
            new SqlParameter("@Url",PhotoUrl),
            new SqlParameter("@SmallPhotoUrl",SmallPhotoUrl),
            new SqlParameter("@UserId",User_Id)
            };
            SqlCommand cmd = Helper.GetCommand(CmdText, parm);
            try
            {
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    int id = Int32.Parse(sdr["_PhotoId"].ToString());
                    sdr.Dispose();
                    return id;
                }
                return -1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region [更新图片信息]

        /// <summary>
        /// 更新图片信息,返回相册id和userId，用于最新动态
        /// </summary>
        /// <param name="PhotoId"></param>
        /// <param name="Info"></param>
        /// <param name="Time"></param>
        /// <param name="PhotoUrl"></param>
        public int[] UpdatePhoto(int PhotoId, string Name, string Info, DateTime Time, string PhotoUrl)
        {
            SqlHelper Helper = new SqlHelper();
            string CmdText = "UPDATE _Photo SET _PhotoName=@Name,_PhotoContent=@Info,_PhotoUpdateTime=@Time," +
                "_PhotoUrl=@Url WHERE _PhotoId=@PId Select @@identity as _PhotoOfAlbumId " +
                "Select * From _Photo Where PhotoId = @PId";
            SqlParameter[] parm = new SqlParameter[]
            {
            new SqlParameter("@Name",Name),
            new SqlParameter("@PId",PhotoId),
            new SqlParameter("@Info",Info),
            new SqlParameter("@Time",Time),
            new SqlParameter("@Url",PhotoUrl)
            };
            SqlCommand mycommand = Helper.GetCommand(CmdText, parm);
            try
            {
                SqlDataReader sdr = mycommand.ExecuteReader();
                int[] Ids = new int[2];
                if (sdr.Read())
                {
                    Ids[0] = Int32.Parse(sdr["_PhotoOfAlbumId"].ToString());
                    Ids[1] = Int32.Parse(sdr["_PhotoOfUId"].ToString());
                }
                return Ids;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region [删除图片]

        /// <summary>
        /// 删除图片信息
        /// </summary>
        /// <param name="PhotoId"></param>
        public void DeletePhoto(int PhotoId)
        {
            SqlHelper Helper = new SqlHelper();
            string CmdText = "DELETE _Photo WHERE _PhotoId = @PId";
            SqlParameter[] parm = new SqlParameter[]
            {
              new SqlParameter("@PId",PhotoId)
            };

            Album_Photo album_photo = new Album_Photo();
            PhotoClass photo = album_photo.GetPhotoInf(PhotoId);
            if (photo != null)
            {
                System.IO.FileInfo file;
                string path = System.Web.HttpContext.Current.Server.MapPath(photo.PhotoUrl);//项目的程序集里必须这么用
                file = new System.IO.FileInfo(path);
                if (file.Exists) file.Delete();
            }
            SqlCommand mycommand = Helper.GetCommand(CmdText, parm);
            try
            {
                mycommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region [返回图片所有信息]

        public PhotoClass GetPhotoInf(int PhotoId)
        {

            PhotoClass photo;
            SqlHelper Helper = new SqlHelper();
            string CmdText = "SELECT * FROM _Photo WHERE _PhotoId =@PId";
            SqlParameter[] parm = new SqlParameter[]
            {
                new SqlParameter("@PId",PhotoId)
            };

            SqlCommand mycommand = Helper.GetCommand(CmdText, parm);
            try
            {
                SqlDataReader dr = mycommand.ExecuteReader();
                if (dr.Read())
                {
                    photo = new PhotoClass(Convert.ToInt32(dr["_PhotoId"]), Convert.ToInt32(dr["_PhotoOfAlbumId"]),
                        dr["_PhotoName"].ToString(), dr["_PhotoContent"].ToString(), dr["_PhotoUrl"].ToString(),
                        DateTime.Parse(Convert.ToString(dr["_PhotoAddTime"])), DateTime.Parse(Convert.ToString(dr["_PhotoUpdateTime"])));
                    return photo;
                }
                else return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        #endregion

        #region[获取最新图片列表]
        /// <summary>
        /// 获取最新图片列表（自己的）
        /// </summary>
        /// <param name="nUId"></param>
        /// <param name="nNum">返回的图片张数</param>
        /// <returns></returns>
        public DataTable GetPhotoList(int nUId, int nNum)
        {

            string _smdStr = "select  * from _Photo   where _PhotoOfUId ='" + nUId.ToString() + "'";
            OwnSqlHelper _shp = new OwnSqlHelper();
            try
            {
                DataTable _dt = _shp.ExecuteQuery(_smdStr, System.Data.CommandType.Text);
                _dt.DefaultView.Sort = "_PhotoUpdateTime desc";
                int i;

                for (i = 0; i < _dt.Rows.Count; i++)
                {
                    if (i < _dt.Rows.Count - 5)
                    {
                        _dt.Rows[i].Delete();
                    }
                    else
                    {
                        _dt.Rows[i]["_PhotoUrl"] = "../" + _dt.Rows[i]["_PhotoUrl"].ToString();
                    }
                }
                return _dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

    }
}